package com.nbjtjc.safe.mapper.edu;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.nbjtjc.safe.model.edu.EduRequirementSurveyItem;
import com.nbjtjc.safe.model.emg.handle.EmgHandleGoods;
import com.nbjtjc.util.MyMapper;
@Mapper
public interface EduRequirementSurveyItemMapper extends MyMapper<EduRequirementSurveyItem> {
    @Select("select * from edu_requirement_survey_item where req_invest_id=#{reqInvestId}")
    List<EduRequirementSurveyItem> findByReqInvestId(@Param("reqInvestId") int reqInvestId);
    
    @Delete("delete  from edu_requirement_survey_item where req_invest_id=#{reqInvestId}")
    int deleteByReqInvestId(@Param("reqInvestId") int reqInvestId);
  	
    @Update("UPDATE edu_requirement_survey_item r SET r.count = ( SELECT t.count FROM"
+"(SELECT items_id,IFNULL(SUM(IFNULL(people,0)),0) AS `count` FROM edu_requirement_items_survey_results a INNER JOIN "
+"edu_requirement_user_survey_results b ON a.user_survey_result_id = b.id WHERE b.req_invest_id = #{reqInvestId} AND b.status = 1 AND a.item_result = 1 GROUP BY items_id)t "
+ "WHERE r.id = t.items_id)")
    int updateCountItemsByReqInvestId(@Param("reqInvestId") int reqInvestId); 
//    @Update("UPDATE edu_requirement_survey_item r SET r.count = ( SELECT t.count FROM"
//+"(SELECT items_id,IFNULL(SUM(IFNULL(people,0)),0) AS `count` FROM edu_requirement_items_survey_results a INNER JOIN "
//+"edu_requirement_user_survey_results b ON a.user_survey_result_id = b.id WHERE b.req_invest_id = #{reqInvestId} AND b.status = 1  GROUP BY items_id)t "
//+ "WHERE r.id = t.items_id)")
//    int updateCountItemsByReqInvestId(@Param("reqInvestId") int reqInvestId); 
    
    @Insert("INSERT INTO edu_requirement_survey_item (req_invest_id,items) (SELECT #{reqInvestId} AS req_invest_id,items FROM edu_requirement_survey_items_templet)")
    int insertTemplet(@Param("reqInvestId") int reqInvestId);
    
    @Insert("INSERT INTO edu_requirement_survey_item (req_invest_id,items) (SELECT #{reqInvestId} AS req_invest_id,items FROM edu_requirement_survey_item WHERE req_invest_id = (SELECT MAX(IFNULL(id,0)) FROM edu_requirement_investigation WHERE status!=3 and id != #{reqInvestId}))")
    int insertLastItems(@Param("reqInvestId") int reqInvestId);
    
    @Select("select * from edu_requirement_survey_item where req_invest_id = #{reqInvestId}")
	ArrayList<Integer> findIdByReqInvestId(@Param("reqInvestId") Integer id);

    @Insert(" INSERT INTO edu_requirement_survey_item(req_invest_id,items,item_temp_id) "
    		+"(SELECT #{reqInvestId} AS req_invest_id,item AS items,id AS item_temp_id FROM edu_requirement_survey_templet WHERE cancel = 0)")
	int insertItemByTemplet(@Param("reqInvestId") Integer id);
}

